package com.ruoyi.tongji.run;

import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.DateUtils;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;

import java.io.File;
import java.util.*;

public class 员工任务指标查询 {


    public static Date dateRangeMonth = DateUtil.getDate("2024-11-30");
    public static String root = "E:\\文档\\2024\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\程序查询";

    public static void main1(String[] args) {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();
        SQLManager jiangzhanManager = ManagerFactory.jiangzhanSqlManager();

        File rootFile = new File(root);
        rootFile.mkdirs();
        //if(true) return ;
        List memberList = jiangzhanManager.execute(new SQLReady("select * from member", new Object[] {}), HashMap.class);

        List dataList = new ArrayList();
        int i = 1;
        for (Iterator<Map<String, String>> iterator = memberList.iterator(); iterator.hasNext();) {
            i++;
            Map<String, String> m = iterator.next();
            System.out.println(m.get("name"));
            String remark = m.get("name");
            String start_date = "20250101";
            String end_date = "20251231";
            String start_time = start_date + "0000000";
            String end_time = end_date + "2300000";
            String pre_start_date = "20240101";
            String pre_end_date = "20241231";

            List<HashMap> resultList = taibangSqlManager.execute(new SQLReady(
                    "select *,献浆总袋数/献浆总人数 as 频次, 新浆员献浆一次人数/新浆员数 as 新浆员一次率, 献浆一次人数/献浆总人数 as 一次率, (献浆总人数-新浆员数)/上年献浆总人数 as 留存率,\"\" as \"^^^^\",\n" +
                    "浆站献浆总袋数/浆站献浆总人数 as 浆站频次, 浆站新浆员献浆一次人数/浆站新浆员数 as 浆站新浆员一次率, 浆站献浆一次人数/浆站献浆总人数 as 浆站一次率, (浆站献浆总人数-浆站新浆员数)/浆站上年献浆总人数 as 浆站留存率\n" +
                    "\n" +
                    "from (\n" +
                    "select ? as 备注,\n" +
                    "(select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ?) as 献浆总袋数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId) as a) as 献浆总人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId having count(*) = 1) as a) as 献浆一次人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? and d.issuingDate between ? and ? and d.firstCollectDate between ? and ? group by d.donorId having count(*) = 1) as a) as 新浆员献浆一次人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId) as a) as 上年献浆总人数,\n" +
                    "(select count(*) from t_bims_donor as d where d.issuingDate between ? and ? and d.firstCollectDate between ? and ? and (d.remark like CONCAT(\"%\",?,\"%\"))) as 新浆员数,\n" +
                    "'' as '^^^^',\n" +
                    "(select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ?) as 浆站献浆总袋数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId) as a) as 浆站献浆总人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId having count(*) = 1) as a) as 浆站献浆一次人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? and d.issuingDate between ? and ? and d.firstCollectDate between ? and ? group by d.donorId having count(*) = 1) as a) as 浆站新浆员献浆一次人数,\n" +
                    "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId) as a) as 浆站上年献浆总人数,\n" +
                    "(select count(*) from t_bims_donor as d where d.issuingDate between ? and ? and d.firstCollectDate between ? and ? ) as 浆站新浆员数\n" +
                    "\n" +
                    ") as a",
                    new Object[] {remark, 
                            remark,start_date, end_date,
                            remark,start_date, end_date,
                            remark,start_date, end_date,
                            remark,start_date, end_date,start_time, end_time,start_time, end_time,
                            remark,pre_start_date, pre_end_date,
                            start_time, end_time,start_time, end_time,remark,

                            start_date, end_date,
                            start_date, end_date,
                            start_date, end_date,
                            start_date, end_date,start_time, end_time,start_time, end_time,
                            pre_start_date, pre_end_date,
                            start_time, end_time,start_time, end_time
                    }), HashMap.class);
            HashMap data = resultList.get(0);
            dataList.add(data);
        }
        System.out.println(dataList);
        for (Iterator iterator = dataList.iterator(); iterator.hasNext(); ) {
            HashMap data = (HashMap) iterator.next();
            System.out.print(data.get("备注") + "\t");
            System.out.print(data.get("献浆总袋数") + "\t");
            System.out.print(data.get("献浆总人数") + "\t");
            System.out.print(data.get("献浆一次人数") + "\t");
            System.out.print(data.get("新浆员献浆一次人数") + "\t");
            System.out.print(data.get("上年献浆总人数") + "\t");
            System.out.print(data.get("新浆员数") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("浆站献浆总袋数") + "\t");
            System.out.print(data.get("浆站献浆总人数") + "\t");
            System.out.print(data.get("浆站献浆一次人数") + "\t");
            System.out.print(data.get("浆站新浆员献浆一次人数") + "\t");
            System.out.print(data.get("浆站上年献浆总人数") + "\t");
            System.out.print(data.get("浆站新浆员数") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("频次") + "\t");
            System.out.print(data.get("新浆员一次率") + "\t");
            System.out.print(data.get("一次率") + "\t");
            System.out.print(data.get("留存率") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("浆站频次") + "\t");
            System.out.print(data.get("浆站新浆员一次率") + "\t");
            System.out.print(data.get("浆站一次率") + "\t");
            System.out.print(data.get("浆站留存率") + "\t");
            System.out.println();
        }
    }


    public static void main(String[] args) {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();
        SQLManager jiangzhanManager = ManagerFactory.jiangzhanSqlManager();

        File rootFile = new File(root);
        rootFile.mkdirs();
        //if(true) return ;
        List 宣传员List = jiangzhanManager.execute(new SQLReady("select * from 宣传员 where type=?", new Object[] {"正常宣传员"}), HashMap.class);

        List dataList = new ArrayList();
        int i = 1;
        for (Iterator<Map<String, String>> iterator = 宣传员List.iterator(); iterator.hasNext();) {
            i++;
            Map<String, String> m = iterator.next();
            System.out.println(m.get("name"));
            String remark = m.get("linkmanno");
            String name = m.get("name");
            String start_date = "20240101";
            String end_date = "20241231";
            String start_time = start_date + "0000000";
            String end_time = end_date + "2300000";
            String pre_start_date = "20240101";
            String pre_end_date = "20241231";

            List<HashMap> resultList = taibangSqlManager.execute(new SQLReady(
                    "select *,献浆总袋数/献浆总人数 as 频次, 新浆员献浆一次人数/新浆员数 as 新浆员一次率, 献浆一次人数/献浆总人数 as 一次率, (献浆总人数-新浆员数)/上年献浆总人数 as 留存率,\"\" as \"^^^^\",\n" +
                            "浆站献浆总袋数/浆站献浆总人数 as 浆站频次, 浆站新浆员献浆一次人数/浆站新浆员数 as 浆站新浆员一次率, 浆站献浆一次人数/浆站献浆总人数 as 浆站一次率, (浆站献浆总人数-浆站新浆员数)/浆站上年献浆总人数 as 浆站留存率\n" +
                            "\n" +
                            "from (\n" +
                            "select ? as 备注,\n" +
                            "(select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ?) as 献浆总袋数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId) as a) as 献浆总人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId having count(*) = 1) as a) as 献浆一次人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? and d.issuingDate between ? and ? and d.firstCollectDate between ? and ? group by d.donorId having count(*) = 1) as a) as 新浆员献浆一次人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and d.remark like CONCAT(\"%\",?,\"%\") and f.recordDate between ? and ? group by d.donorId) as a) as 上年献浆总人数,\n" +
                            "(select count(*) from t_bims_donor as d where d.issuingDate between ? and ? and d.firstCollectDate between ? and ? and (d.remark like CONCAT(\"%\",?,\"%\"))) as 新浆员数,\n" +
                            "'' as '^^^^',\n" +
                            "(select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ?) as 浆站献浆总袋数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId) as a) as 浆站献浆总人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId having count(*) = 1) as a) as 浆站献浆一次人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? and d.issuingDate between ? and ? and d.firstCollectDate between ? and ? group by d.donorId having count(*) = 1) as a) as 浆站新浆员献浆一次人数,\n" +
                            "(select count(*) from (select count(*) from t_bims_donor_collect_flow f left join t_bims_donor d on f.donorId = d.donorId where f.plasmaCollection = 'Y' and f.recordDate between ? and ? group by d.donorId) as a) as 浆站上年献浆总人数,\n" +
                            "(select count(*) from t_bims_donor as d where d.issuingDate between ? and ? and d.firstCollectDate between ? and ? ) as 浆站新浆员数\n" +
                            "\n" +
                            ") as a",
                    new Object[] {name,
                            remark,start_date, end_date,
                            remark,start_date, end_date,
                            remark,start_date, end_date,
                            remark,start_date, end_date,start_time, end_time,start_time, end_time,
                            remark,pre_start_date, pre_end_date,
                            start_time, end_time,start_time, end_time,remark,

                            start_date, end_date,
                            start_date, end_date,
                            start_date, end_date,
                            start_date, end_date,start_time, end_time,start_time, end_time,
                            pre_start_date, pre_end_date,
                            start_time, end_time,start_time, end_time
                    }), HashMap.class);
            HashMap data = resultList.get(0);
            dataList.add(data);
        }
        System.out.println(dataList);
        for (Iterator iterator = dataList.iterator(); iterator.hasNext(); ) {
            HashMap data = (HashMap) iterator.next();
            System.out.print(data.get("备注") + "\t");
            System.out.print(data.get("献浆总袋数") + "\t");
            System.out.print(data.get("献浆总人数") + "\t");
            System.out.print(data.get("献浆一次人数") + "\t");
            System.out.print(data.get("新浆员献浆一次人数") + "\t");
            System.out.print(data.get("上年献浆总人数") + "\t");
            System.out.print(data.get("新浆员数") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("浆站献浆总袋数") + "\t");
            System.out.print(data.get("浆站献浆总人数") + "\t");
            System.out.print(data.get("浆站献浆一次人数") + "\t");
            System.out.print(data.get("浆站新浆员献浆一次人数") + "\t");
            System.out.print(data.get("浆站上年献浆总人数") + "\t");
            System.out.print(data.get("浆站新浆员数") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("频次") + "\t");
            System.out.print(data.get("新浆员一次率") + "\t");
            System.out.print(data.get("一次率") + "\t");
            System.out.print(data.get("留存率") + "\t");
            System.out.print("^^^^\t");
            System.out.print(data.get("浆站频次") + "\t");
            System.out.print(data.get("浆站新浆员一次率") + "\t");
            System.out.print(data.get("浆站一次率") + "\t");
            System.out.print(data.get("浆站留存率") + "\t");
            System.out.println();
        }
    }
}
